<?php
class Model_Appointment extends Model_Template
{
	private $className2;	

	public function __construct()
	{
		parent::__construct();
		$this->className = 'Model_Table_Appointment';
		$this->className2 = 'Model_Table_ProcedureHasAppointment';

	}
	///////////////////////////////////////////////////////////get an appointment by id
	public function getAppointmentById($appointmentId)
	{
		//Select one appointment by ID query
		$sqlSelectAppointmentById = 'SELECT * FROM appointment WHERE appointment_id=?;';
	
		//Prepare statement
		$selectAppointmentById = Controller_Template::$db->prepareEKADBPDO($sqlSelectAppointmentById, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
	
		//Execute statement
		$selectAppointmentById->execute(array($appointmentId));
	
		return $selectAppointmentById->fetch();
	}
	///////////////////////////////////////////////////////////get all procedures from one appointment
	public function getProceduresByAppointment($appointmentId)
	{
		//select all procedure from one appointment
		$sqlSelectProceduresByAppointment='SELECT * FROM Procedure_has_Appointment WHERE Appointment_appointment_id=?;';
		
		//prepare statement
		$selectProceduresByAppointment=Controller_Template::$db->prepareEKADBPDO($sqlSelectProceduresByAppointment, $this->className2, array('procedure_id','appointment_id'));
		
		//execute statement
		$selectProceduresByAppointment->execute(array($appointmentId));
		
		return $selectProceduresByAppointment->fetchAll();
	}
	
	////////////////////////////////////////////////////////////////get all appointments for one profile
	public function getAllAppointmentsByProfileId($profileId)
	{
		//Select appointments with one profile query
		$sqlSelectAppointment = 'SELECT * FROM appointment WHERE Profile_profile_id=? AND status="open"
								ORDER BY date,start_time;';
	
		//Prepare statement
		$selectAppointment = Controller_Template::$db->prepareEKADBPDO($sqlSelectAppointment, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
	
		//Execute statement
		$selectAppointment->execute(array($profileId));
	
		return $selectAppointment->fetchAll();
	}
	
	
	///////////////////////////////////////////////////////////////get appointments for 7 days starting on monday given any date of the year
	public function getOneWeekAppointments($startDate)
	{
		///////////////////////////////////////////////find week with any given date
		$md=strtotime($startDate);
	
		while(date('D',$md)!='Mon')//////loop untill monday
		{
			$md-=60*60*24;/////take off one day
		}
	
		$monday=date('Y-m-d', $md);
	
		$endDate=date('Y-m-d', mktime(date('h',$md),date('i',$md), date('s',$md), date('m',$md)+0,date('d',$md)+6, date('Y',$md)+0));//find sunday given a monday
	
	
		//////////////////////////////////////////////////
		//Select one week appointment query
		$sqlSelectAppointment = 'SELECT * FROM appointment WHERE date>=? AND date<=? AND status="open"
								ORDER BY date,start_time;';
	
		//Prepare statement
		$selectAppointment = Controller_Template::$db->prepareEKADBPDO($sqlSelectAppointment, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
	
		//Execute statement
		$selectAppointment->execute(array($monday,$endDate));
	
		return $selectAppointment->fetchAll();
	}
		
	/////////////////////////////////////////////////////////////////////////////removes all procedures assigned to one appointment
	public function removeAllProceduresFromAppointment($appointmentId)
	{
		//remove all procedures from appointment query
		$sqlRemoveAllProceduresFromAppointment='DELETE FROM Procedure_has_Appointment WHERE Appointment_appointment_id=?;';
		
		//prepare statement
		$removeAllProceduresFromAppointment=Controller_Template::$db->prepareEKADBPDO($sqlRemoveAllProceduresFromAppointment, $this->className2, array('procedure_id','appointment_id'));
		
		//execute statement
		$removeAllProceduresFromAppointment->execute(array($appointmentId));
		
		return $removeAllProceduresFromAppointment->rowCount();
		
	}
	
	////////////////////////////////////////////////////////////////////////////add one procedure to an appointment
	public function addProcedureToAppointment($procedureId,$appointmentId)
	{
		$sqlAddProcedure = 'INSERT INTO Procedure_has_Appointment (Procedure_procedure_Id, Appointment_appointment_id) VALUES (?,?);';
		
		//Prepare statement
		$addProcedure = Controller_Template::$db->prepareEKADBPDO($sqlAddProcedure, $this->className2, array('procedure_id','appointment_id'));
		
		//Execute statement
		$addProcedure->execute(array($procedureId,$appointmentId));
		
		return $addProcedure->rowCount();

	}
	
	/////////////////////////////////////////////////////returns all appointment for one specific date
	public function getAppointmentByDate($date)
	{
		//select all appoior that date query
		$sqlSelectAppointmentByDate = 'SELECT * FROM appointment WHERE date=? AND status="open";';
	
		//Prepare statement
		$selectAppointmentByDate = Controller_Template::$db->prepareEKADBPDO($sqlSelectAppointmentByDate, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
	
		//Execute statement
		$selectAppointmentByDate->execute(array($date));
	
		return $selectAppointmentByDate->fetchAll();
	}
	
	//////////////////////////////////////////////////////////////////////////create an appointment with all assigned procedure(this function see if this appointment is in conflic with others if a conflict occurs function returns -2)
	public function createNewAppointment($newA, $procedureList)
	{
	
		////////////////////////////////////////////////////check if appointment is valid
		$conflict=false;
		
		$existingAppointments = self::getAppointmentByDate($newA[':date']);
		
		$EmptyTestArray = array_filter($existingAppointments);
	
		if(!empty($EmptyTestArray))
		{
			foreach($existingAppointments as $a)
			{
				if($newA[':start_time'] < $a->getStartTime())
				{
					if($newA[':end_time'] > $a->getStartTime())
					{
						
						$conflict=true;
					}
				}
				elseif($newA[':start_time'] >= $a->getStartTime())
				{
					if($newA[':start_time'] < $a->getEndTime())
					{
						$conflict=true;
					}
				}
			}
		}

		if(!$conflict)
		{
			///////////////////////////////////////////////////
			//Create new appointment query
			$sqlCreateNewAppointment = 'INSERT INTO appointment (date,start_time,end_time,total_estimate_time,status,Profile_profile_id,Car_car_id) VALUES (?,?,?,?,?,?,?);';
				
			//Prepare statement
			$createNewAppointment = Controller_Template::$db->prepareEKADBPDO($sqlCreateNewAppointment, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
				
			//Execute statement
			$createNewAppointment->execute(array(date($newA[':date']),(int)$newA[':start_time'],(int)$newA[':end_time'],(int)$newA[':total_estimate_time'],$newA[':status'],$newA[':profile_id'],$newA[':car_id']));
				
			$lastAppointmentId=Controller_Template::$db->lastInsertId();
			
			$EmptyTestArray = array_filter($procedureList);
			if(!empty($EmptyTestArray))
			{
				foreach($procedureList as $p)
				{
					self::addProcedureToAppointment($p,$lastAppointmentId);
				}
			}
				
			if($createNewAppointment->rowCount()==1)
			{
				return $lastAppointmentId;
			}
		}
		else
		{
			return -2;
		}
	}
	
	///////////////////////////////////////////////////////////////////////delete an appointment
	public function deleteAppointment($appointmentId)
	{
		//Delete appointment query
		$sqlDeleteAppointment = 'DELETE FROM appointment WHERE appointment_id=?;';
	
		//Prepare statement
		$deleteAppointment = Controller_Template::$db->prepareEKADBPDO($sqlDeleteAppointment, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
	
		//Execute statement
		$deleteAppointment->execute(array($appointmentId));
	
		return $deleteAppointment->rowCount();
	}
	
	
	/////////////////////////////////////////////////////////////////////updates an appointment with all assigned procedures
	public function updateAppointment($targetAppointment, $procedureList)
	{
		//Delete appointment query
		$sqlUpdateAppointment = 'UPDATE appointment SET date=?,start_time=?,end_time=?,total_estimate_time=?,status=?,Profile_profile_id=?,Car_car_id=? WHERE appointment_id=?;';
	
		//Prepare statement
		$updateAppointment = Controller_Template::$db->prepareEKADBPDO($sqlUpdateAppointment, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
	
		//Execute statement
		$updateAppointment->execute(array($targetAppointment[':date'],$targetAppointment[':start_time'],$targetAppointment[':end_time'],$targetAppointment[':total_estimate_time'],$targetAppointment[':status'],$targetAppointment[':profile_id'],$targetAppointment[':car_id'],$targetAppointment[':appointment_id']));
	
	
		self::removeAllProceduresFromAppointment($targetAppointment[':appointment_id']);
	
		$EmptyTestArray = array_filter($procedureList);
		if(!empty($EmptyTestArray))
		{
			foreach($procedureList as $p)
			{
				self::addProcedureToAppointment($p,$targetAppointment[':appointment_id']);
			}
		}
	
	
		return $updateAppointment->rowCount();
	}
	
	////////////////////////////////////////////////////////////////////////update appointment status
	public function setAppointmentStatus($appointmentId,$status)
	{
		//update appointment status sql
		$sqlUpdateAppointmentStatus='UPDATE appointment SET status=? WHERE appointment_id=?;';
		
		//prepare statement
		$updateAppointmentStatus = Controller_Template::$db->prepareEKADBPDO($sqlUpdateAppointmentStatus, $this->className, array('appointment_id','date','start_time','end_time','total_estimate_time','status','Profile_profile_id','Car_car_id'));
		
		//execute statement
		$updateAppointmentStatus->execute(array($status,$appointmentId));
		
		return $updateAppointmentStatus->rowCount();
	}
	
	///////////////////////////////////////////////////////////////////////// append procedures to car history
	public function updateCarbHistory($carId,$procedureName)
	{

			$notNullStr="\r\n".$procedureName;
			//set car history query
			$sqlSetCarHistory='UPDATE car SET car_history = IFNULL(CONCAT(?, car_history), ?) WHERE car_id = ?;';
			
			//prepare statement
			$setCarHistory=Controller_Template::$db->prepare($sqlSetCarHistory);
			
			//execute statement
			$setCarHistory->execute(array($notNullStr,$procedureName,$carId));
			
			
			return $setCarHistory->rowCount();

	}
	/////////////////////////////////////////////////////////////////////// update oil change date for car
	public function updateOilChangeDate($carId)
	{
		
		//set car oil change query
		$sqlSetCarOilChange='UPDATE car SET last_oil_change_date=? WHERE car_id=?';
			
		//prepare statement
		$setCarOilChange=Controller_Template::$db->prepare($sqlSetCarOilChange);
			
		//execute statement
		$setCarOilChange->execute(array(date("Y-m-d"),$carId));
		
		return $setCarOilChange->rowCount();

		
	}
	//////////////////////////////////////////////////////////////////////// end appointment
	public function endAppointment($appointmentId,$carId,$procedureList)
	{
		$totalRowAffected=0;
		
		$EmptyTestArray = array_filter($procedureList);
		if(!empty($EmptyTestArray))
		{
			foreach($procedureList as $p)////////////////////////////////////////////////////////////appending appointment procedures to car history
			{
				if (stristr($p, "oil change"))/////////////////oil change detected
				{
					//set oil change date
					self::updateOilChangeDate($carId);
				}
				$totalRowAffected+=self::updateCarbHistory($carId,"+".$p);
			}
		}
		
		if($totalRowAffected == count($procedureList))
		{
			$totalRowAffected+=self::updateCarbHistory($carId,"----------".date("Y/m/d")."----------");////////////////////////////create header/seperator
		}
		
		if($totalRowAffected == 1 + count($procedureList))
		{
			$totalRowAffected+=self::setAppointmentStatus($appointmentId,'closed');//////////////////////////////////////set appointment status to closed
		}
		
		
		if($totalRowAffected == 2 + count($procedureList))
		{
			return true;
		}
		else
		{
			return false;
		}
	}
}